<!DOCTYPE html>
<html>
<head>
  <meta charset="utf-8">
  
  <title>Mysql用户和权限管理 | 上海科鹄网络科技有限公司</title>
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
  <meta name="description" content="mysql 用户和权限管理操作命令">
<meta property="og:type" content="article">
<meta property="og:title" content="Mysql用户和权限管理">
<meta property="og:url" content="http://ikohoo.com/blog/2017/09/19/mysql-user/index.html">
<meta property="og:site_name" content="上海科鹄网络科技有限公司">
<meta property="og:description" content="mysql 用户和权限管理操作命令">
<meta property="og:updated_time" content="2017-09-19T05:43:52.664Z">
<meta name="twitter:card" content="summary">
<meta name="twitter:title" content="Mysql用户和权限管理">
<meta name="twitter:description" content="mysql 用户和权限管理操作命令">
  
    <link rel="alternate" href="/atom.xml" title="上海科鹄网络科技有限公司" type="application/atom+xml">
  
  
    <link rel="icon" href="/favicon.png">
  
  
    <link href="//fonts.googleapis.com/css?family=Source+Code+Pro" rel="stylesheet" type="text/css">
  
  <link rel="stylesheet" href="/blog/css/style.css">
  

</head>

<body>
  <div id="container">
    <div id="wrap">
      <header id="header">
  <div id="banner"></div>
  <div id="header-outer" class="outer">
    <div id="header-title" class="inner">
      <h1 id="logo-wrap">
        <a href="/blog/" id="logo">上海科鹄网络科技有限公司</a>
      </h1>
      
        <h2 id="subtitle-wrap">
          <a href="/blog/" id="subtitle">国内领先的云计算和大数据解决方案咨询服务商</a>
        </h2>
      
    </div>
    <div id="header-inner" class="inner">
      <nav id="main-nav">
        <a id="main-nav-toggle" class="nav-icon"></a>
        
          <a class="main-nav-link" href="/blog/">Home</a>
        
          <a class="main-nav-link" href="/blog/archives">Archives</a>
        
      </nav>
      <nav id="sub-nav">
        
          <a id="nav-rss-link" class="nav-icon" href="/atom.xml" title="Flux RSS"></a>
        
        <a id="nav-search-btn" class="nav-icon" title="Rechercher"></a>
      </nav>
      <div id="search-form-wrap">
        <form action="//google.com/search" method="get" accept-charset="UTF-8" class="search-form"><input type="search" name="q" class="search-form-input" placeholder="Search"><button type="submit" class="search-form-submit">&#xF002;</button><input type="hidden" name="sitesearch" value="http://ikohoo.com/blog"></form>
      </div>
    </div>
  </div>
</header>
      <div class="outer">
        <section id="main"><article id="post-mysql-user" class="article article-type-post" itemscope itemprop="blogPost">
  <div class="article-meta">
    <a href="/blog/2017/09/19/mysql-user/" class="article-date">
  <time datetime="2017-09-19T05:40:37.000Z" itemprop="datePublished">2017-09-19</time>
</a>
    
  </div>
  <div class="article-inner">
    
    
      <header class="article-header">
        
  
    <h1 class="article-title" itemprop="name">
      Mysql用户和权限管理
    </h1>
  

      </header>
    
    <div class="article-entry" itemprop="articleBody">
      
        <p>mysql 用户和权限管理操作命令</p>
<a id="more"></a>
<h1 id="一-创建用户"><a href="#一-创建用户" class="headerlink" title="一, 创建用户:"></a>一, 创建用户:</h1><p>命令:CREATE USER ‘username’@’host’ IDENTIFIED BY ‘password’;</p>
<p>说明:username - 你将创建的用户名, host - 指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost, 如果想让该用户可以从任意远程主机登陆,可以使用通配符%. password - 该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器.</p>
<p>例子: CREATE USER ‘username’@’localhost’ IDENTIFIED BY ‘123456’;<br>CREATE USER ‘username’@’192.168.1.101_’ IDENDIFIED BY ‘123456’;<br>CREATE USER ‘username’@’%’ IDENTIFIED BY ‘123456’;<br>CREATE USER ‘username’@’%’ IDENTIFIED BY ‘’;<br>CREATE USER ‘username’@’%’;</p>
<h1 id="二-授权"><a href="#二-授权" class="headerlink" title="二,授权:"></a>二,授权:</h1><p>命令:GRANT privileges ON databasename.tablename TO ‘username’@’host’</p>
<p>说明: privileges - 用户的操作权限,如SELECT , INSERT , UPDATE 等(详细列表见该文最后面).如果要授予所的权限则使用ALL.;databasename - 数据库名,tablename-表名,如果要授予该用户对所有数据库和表的相应操作权限则可用<em>表示, 如</em>.*.</p>
<p>例子: GRANT SELECT, INSERT ON test.user TO ‘username’@’%’;<br>GRANT ALL ON <em>.</em> TO ‘username’@’%’;</p>
<p>注意:用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用以下命令:<br>GRANT privileges ON databasename.tablename TO ‘username’@’host’ WITH GRANT OPTION;</p>
<h1 id="三-设置与更改用户密码"><a href="#三-设置与更改用户密码" class="headerlink" title="三.设置与更改用户密码"></a>三.设置与更改用户密码</h1><p>命令:SET PASSWORD FOR ‘username’@’host’ = PASSWORD(‘newpassword’);如果是当前登陆用户用SET PASSWORD = PASSWORD(“newpassword”);</p>
<p>例子: SET PASSWORD FOR ‘username’@’%’ = PASSWORD(“123456”);</p>
<h1 id="四-撤销用户权限"><a href="#四-撤销用户权限" class="headerlink" title="四.撤销用户权限"></a>四.撤销用户权限</h1><p>命令: REVOKE privilege ON databasename.tablename FROM ‘username’@’host’;</p>
<p>说明: privilege, databasename, tablename - 同授权部分.</p>
<p>例子: REVOKE SELECT ON <em>.</em> FROM ‘username’@’%’;</p>
<p>注意: 假如你在给用户’username’@’%’授权的时候是这样的(或类似的):GRANT SELECT ON test.user TO ‘username’@’%’, 则在使用REVOKE SELECT ON <em>.</em> FROM ‘username’@’%’;命令并不能撤销该用户对test数据库中user表的SELECT 操作.相反,如果授权使用的是GRANT SELECT ON <em>.</em> TO ‘username’@’%’;则REVOKE SELECT ON test.user FROM ‘username’@’%’;命令也不能撤销该用户对test数据库中user表的Select 权限.</p>
<p>具体信息可以用命令SHOW GRANTS FOR ‘username’@’%’; 查看.</p>
<h1 id="五-删除用户"><a href="#五-删除用户" class="headerlink" title="五.删除用户"></a>五.删除用户</h1><p>命令: DROP USER ‘username’@’host’;</p>
<h1 id="附表-在MySQL中的操作权限"><a href="#附表-在MySQL中的操作权限" class="headerlink" title="附表:在MySQL中的操作权限"></a>附表:在MySQL中的操作权限</h1><p>ALTER</p>
<p>Allows use of ALTER TABLE.</p>
<p>ALTER ROUTINE</p>
<p>Alters or drops stored routines.</p>
<p>CREATE</p>
<p>Allows use of CREATE TABLE.</p>
<p>CREATE ROUTINE</p>
<p>Creates stored routines.</p>
<p>CREATE TEMPORARY TABLE</p>
<p>Allows use of CREATE TEMPORARY TABLE.</p>
<p>CREATE USER</p>
<p>Allows use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES.</p>
<p>CREATE VIEW</p>
<p>Allows use of CREATE VIEW.</p>
<p>DELETE</p>
<p>Allows use of DELETE.</p>
<p>DROP</p>
<p>Allows use of DROP TABLE.</p>
<p>EXECUTE</p>
<p>Allows the user to run stored routines.</p>
<p>FILE</p>
<p>Allows use of SELECT… INTO OUTFILE and LOAD DATA INFILE.</p>
<p>INDEX</p>
<p>Allows use of CREATE INDEX and DROP INDEX.</p>
<p>INSERT</p>
<p>Allows use of INSERT.</p>
<p>LOCK TABLES</p>
<p>Allows use of LOCK TABLES on tables for which the user also has SELECT privileges.</p>
<p>PROCESS</p>
<p>Allows use of SHOW FULL PROCESSLIST.</p>
<p>RELOAD</p>
<p>Allows use of FLUSH.</p>
<p>REPLICATION</p>
<p>Allows the user to ask where slave or master</p>
<p>CLIENT</p>
<p>servers are.</p>
<p>REPLICATION SLAVE</p>
<p>Needed for replication slaves.</p>
<p>SELECT</p>
<p>Allows use of SELECT.</p>
<p>SHOW DATABASES</p>
<p>Allows use of SHOW DATABASES.</p>
<p>SHOW VIEW</p>
<p>Allows use of SHOW CREATE VIEW.</p>
<p>SHUTDOWN</p>
<p>Allows use of mysqladmin shutdown.</p>
<p>SUPER</p>
<p>Allows use of CHANGE MASTER, KILL, PURGE MASTER LOGS, and SET GLOBAL SQL statements. Allows mysqladmin debug command. Allows one extra connection to be made if maximum connections are reached.</p>
<p>UPDATE</p>
<p>Allows use of UPDATE.</p>
<p>USAGE</p>
<p>Allows connection without any specific privileges.</p>
<h1 id="示例"><a href="#示例" class="headerlink" title="示例"></a>示例</h1><p>MySql中添加用户,新建数据库,用户授权,删除用户,修改密码<br>查看已有用户：<br>select user,host,password from mysql.user;</p>
<p>1.新建用户。<br>//登录MYSQL<br>@&gt;mysql -u root -p<br>@&gt;密码<br>//创建用户<br>mysql&gt; mysql&gt; insert into mysql.user(Host,User,Password,ssl_cipher,x509_issuer,x509_sub<br>ject) values(“localhost”,”phplamp”,password(“1234”),’’,’’,’’);<br>这样就创建了一个名为：phplamp 密码为：1234 的用户。<br>然后登录一下。<br>mysql&gt;exit;<br>@&gt;mysql -u phplamp -p<br>@&gt;输入密码<br>mysql&gt;登录成功<br>2.为用户授权。<br>//登录MYSQL（有ROOT权限）。我里我以ROOT身份登录.<br>@&gt;mysql -u root -p<br>@&gt;密码<br>//首先为用户创建一个数据库(phplampDB)<br>mysql&gt;create database phplampDB;<br>//授权phplamp用户拥有phplamp数据库的所有权限。 </p>
<blockquote>
<p>grant all privileges on phplampDB.<em> to phplamp@localhost identified by ‘1234’;<br>//刷新系统权限表<br>mysql&gt;flush privileges;<br>mysql&gt;其它操作<br>/</em><br>如果想指定部分权限给一用户，可以这样来写:<br>mysql&gt;grant select,update on phplampDB.<em> to phplamp@localhost identified by ‘1234’;<br>//刷新系统权限表。<br>mysql&gt;flush privileges; 
</em>/<br>3.删除用户。<br>@&gt;mysql -u root -p<br>@&gt;密码<br>mysql&gt;Delete FROM user Where User=”phplamp” and Host=”localhost”;<br>mysql&gt;flush privileges;<br>//删除用户的数据库<br>mysql&gt;drop database phplampDB;<br>4.修改指定用户密码。<br>@&gt;mysql -u root -p<br>@&gt;密码<br>mysql&gt;update mysql.user set password=password(‘新密码’) where User=”phplamp” and Host=”localhost”;<br>mysql&gt;flush privileges;<br>5.列出所有数据库<br>mysql&gt;show database;<br>6.切换数据库<br>mysql&gt;use ‘数据库名’;<br>7.列出所有表<br>mysql&gt;show tables;<br>8.显示数据表结构<br>mysql&gt;describe 表名;<br>9.删除数据库和数据表<br>mysql&gt;drop database 数据库名;</p>
</blockquote>
<p>注意，如果数据库名包含“.”，那么在删除时需要加上引用引号，如</p>
<p>dropo database <code>php.sql</code>;</p>
<p>mysql&gt;drop table 数据表名;</p>

      
    </div>
    <footer class="article-footer">
      <a data-url="http://ikohoo.com/blog/2017/09/19/mysql-user/" data-id="cja3l1msa001q4stk1j6prw9h" class="article-share-link">Partager</a>
      
      
    </footer>
  </div>
  
    
<nav id="article-nav">
  
    <a href="/blog/2017/09/19/centos-lamp/" id="article-nav-newer" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Récent</strong>
      <div class="article-nav-title">
        
          CentOS下yum安装LAMP
        
      </div>
    </a>
  
  
    <a href="/blog/2017/09/18/unicode/" id="article-nav-older" class="article-nav-link-wrap">
      <strong class="article-nav-caption">Ancien</strong>
      <div class="article-nav-title">Unicode(UTF-8, UTF-16)令人混淆的概念</div>
    </a>
  
</nav>

  
</article>

</section>
        
          <aside id="sidebar">
  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Catégories</h3>
    <div class="widget">
      <ul class="category-list"><li class="category-list-item"><a class="category-list-link" href="/blog/categories/AI/">AI</a></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/Big-Data/">Big Data</a></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/CloudCompute/">CloudCompute</a></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/Efficiency/">Efficiency</a></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/RESTful-API/">RESTful API</a></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/skills/">skills</a></li><li class="category-list-item"><a class="category-list-link" href="/blog/categories/数据仓库/">数据仓库</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Mot-clés</h3>
    <div class="widget">
      <ul class="tag-list"><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/AI/">AI</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Big-Data/">Big Data</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/CloudCompute/">CloudCompute</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/CloudStack/">CloudStack</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Docker/">Docker</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Efficiency/">Efficiency</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Eucalyptus/">Eucalyptus</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/FusionCloud/">FusionCloud</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/HBase/">HBase</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Hadoop/">Hadoop</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Hive/">Hive</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Openstack/">Openstack</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/RESTful/">RESTful</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Resource/">Resource</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Slack/">Slack</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Spark/">Spark</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Team-Management/">Team Management</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/Wiki/">Wiki</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/cloudcompute/">cloudcompute</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/lucene/">lucene</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/markdown/">markdown</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/solr/">solr</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/startup/">startup</a></li><li class="tag-list-item"><a class="tag-list-link" href="/blog/tags/数据仓库/">数据仓库</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Nuage de mot-clés</h3>
    <div class="widget tagcloud">
      <a href="/blog/tags/AI/" style="font-size: 10px;">AI</a> <a href="/blog/tags/Big-Data/" style="font-size: 18px;">Big Data</a> <a href="/blog/tags/CloudCompute/" style="font-size: 16px;">CloudCompute</a> <a href="/blog/tags/CloudStack/" style="font-size: 10px;">CloudStack</a> <a href="/blog/tags/Docker/" style="font-size: 10px;">Docker</a> <a href="/blog/tags/Efficiency/" style="font-size: 14px;">Efficiency</a> <a href="/blog/tags/Eucalyptus/" style="font-size: 10px;">Eucalyptus</a> <a href="/blog/tags/FusionCloud/" style="font-size: 10px;">FusionCloud</a> <a href="/blog/tags/HBase/" style="font-size: 10px;">HBase</a> <a href="/blog/tags/Hadoop/" style="font-size: 10px;">Hadoop</a> <a href="/blog/tags/Hive/" style="font-size: 10px;">Hive</a> <a href="/blog/tags/Openstack/" style="font-size: 10px;">Openstack</a> <a href="/blog/tags/RESTful/" style="font-size: 12px;">RESTful</a> <a href="/blog/tags/Resource/" style="font-size: 10px;">Resource</a> <a href="/blog/tags/Slack/" style="font-size: 12px;">Slack</a> <a href="/blog/tags/Spark/" style="font-size: 10px;">Spark</a> <a href="/blog/tags/Team-Management/" style="font-size: 14px;">Team Management</a> <a href="/blog/tags/Wiki/" style="font-size: 20px;">Wiki</a> <a href="/blog/tags/cloudcompute/" style="font-size: 10px;">cloudcompute</a> <a href="/blog/tags/lucene/" style="font-size: 10px;">lucene</a> <a href="/blog/tags/markdown/" style="font-size: 10px;">markdown</a> <a href="/blog/tags/solr/" style="font-size: 10px;">solr</a> <a href="/blog/tags/startup/" style="font-size: 14px;">startup</a> <a href="/blog/tags/数据仓库/" style="font-size: 10px;">数据仓库</a>
    </div>
  </div>

  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Archives</h3>
    <div class="widget">
      <ul class="archive-list"><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/11/">十一月 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/09/">九月 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/07/">七月 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/06/">六月 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/05/">五月 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2017/02/">二月 2017</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/06/">六月 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/05/">五月 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/03/">三月 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2016/02/">二月 2016</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/12/">十二月 2015</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/11/">十一月 2015</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/10/">十月 2015</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/09/">九月 2015</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/08/">八月 2015</a></li><li class="archive-list-item"><a class="archive-list-link" href="/blog/archives/2015/06/">六月 2015</a></li></ul>
    </div>
  </div>


  
    
  <div class="widget-wrap">
    <h3 class="widget-title">Articles récents</h3>
    <div class="widget">
      <ul>
        
          <li>
            <a href="/blog/2017/11/17/build-warehouse/">搭建数据仓库的流程简介</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/19/centos-lamp/">CentOS下yum安装LAMP</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/19/mysql-user/">Mysql用户和权限管理</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/18/unicode/">Unicode(UTF-8, UTF-16)令人混淆的概念</a>
          </li>
        
          <li>
            <a href="/blog/2017/09/07/restful-api-practice/">RESTful API 设计最佳实践</a>
          </li>
        
      </ul>
    </div>
  </div>

  
</aside>
        
      </div>
      <footer id="footer">
  
  <div class="outer">
    <div id="footer-info" class="inner">
      &copy; 2017 Leo.Liu<br>
      Propulsé by <a href="http://hexo.io/" target="_blank">Hexo</a>
    </div>
  </div>
</footer>
    </div>
    <nav id="mobile-nav">
  
    <a href="/blog/" class="mobile-nav-link">Home</a>
  
    <a href="/blog/archives" class="mobile-nav-link">Archives</a>
  
</nav>
    

<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.3/jquery.min.js"></script>


  <link rel="stylesheet" href="/blog/fancybox/jquery.fancybox.css">
  <script src="/blog/fancybox/jquery.fancybox.pack.js"></script>


<script src="/blog/js/script.js"></script>

  </div>
</body>
</html>